import pymysql

# 定义全值变量
import xlrd

conn = None
cursor = None

# 创建连接
conn = pymysql.connect(host="localhost", port=3306, user="root", password='123456', database='lianxi',
                       charset="utf8")
# 获取游标
cursor = conn.cursor()

# 读取excel文件
FilePath = '/Case/shuju.xls'
# 打开excel文件
wbk = xlrd.open_workbook(FilePath)

# 获取sheet
sheet = wbk.sheet_by_index(0)

# 获取总行数
rows_number = sheet.nrows

# 遍历表中所有航的数据，并保存到一个空列表
cap = []
for i in range(rows_number):
    x = sheet.row_values(i)
    cap.append(x)
print(cap)

# 将读取到的数据批量插入到数据库
for curse in cap:
    cno = curse[0]
    cname = curse[1]
    tno = curse[2]

# 插入语句
    cursor.execute(f"insert into course(cno,cname,tno) value ('{cno}','{cname}','{tno}')")

conn.commit()

conn.close()
print("插入数据完成！")
